﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SCSService
{
    public class UserService
    {
        /// <summary>
        /// Get User by Id 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Security_User GetUserByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var user = data.Security_Users.Where(p => p.UserID == id).FirstOrDefault();
                return user;
            }
        }

        /// <summary>
        /// Create user 
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int CreateUser(Security_User user)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Users.InsertOnSubmit(user);
                data.SubmitChanges();

                return user.UserID;
            }
        }

        /// <summary>
        /// Create Customer
        /// </summary>
        /// <param name="customer"></param>
        /// <returns></returns>
        public int CreateCustomer(Customer customer)
        {
            using (var data = new DataDataContext())
            {
                data.Customers.InsertOnSubmit(customer);
                data.SubmitChanges();

                return customer.CustomerID;
            }
        }

        /// <summary>
        /// Update user
        /// </summary>
        /// <param name="user"></param>
        public void UpdateUser(Security_User user)
        {
            using (var data = new DataDataContext())
            {
                var userUpdate = data.Security_Users.Where(p => p.UserID == user.UserID).SingleOrDefault();

                userUpdate.UserName = user.UserName;
                userUpdate.Password = user.Password;
                userUpdate.RoleID = user.RoleID;
                userUpdate.Email = user.Email;
                userUpdate.UserStatus = user.UserStatus;
                userUpdate.UserDes = user.UserDes;
                userUpdate.RandomText = user.RandomText;
                userUpdate.UndefineOne = user.UndefineOne;
                userUpdate.UndefineTwo = user.UndefineTwo;
                userUpdate.LastModified = DateTime.Now;

                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Update customer
        /// </summary>
        /// <param name="cus"></param>
        public void UpdateCustomer(Customer cus)
        {
            using (var data = new DataDataContext())
            {
                var customer = data.Customers.Where(p => p.UserID == cus.UserID).FirstOrDefault();

                customer.CustomerID = cus.CustomerID;
                customer.CustomerName = cus.CustomerName;
                customer.CustomerCode = cus.CustomerCode;
                customer.Email = cus.Email;
                customer.CompanyName = cus.CompanyName;
                customer.CityID = cus.CityID;
                customer.Balance = cus.Balance;
                customer.Address = cus.Address;
                customer.ActiveDate = cus.ActiveDate;
                customer.CustomerType = cus.CustomerType;
                customer.DistricID = cus.DistricID;
                customer.Mobile = cus.Mobile;
                customer.PrecintID = cus.PrecintID;
                customer.Phone = cus.Phone;
                customer.Status = cus.Status;
                customer.TypeBussiness = cus.TypeBussiness;
                customer.UserID = cus.UserID;

                data.SubmitChanges();
            }
        }

        /// <summary>
        /// change password for user
        /// </summary>
        /// <param name="user"></param>
        public void ChangePassword(Security_User user)
        {
            using (var data = new DataDataContext())
            {
                BaseService baseSvc=new BaseService();
                var userUpdate = data.Security_Users.Where(p => p.UserID == user.UserID).SingleOrDefault();

                userUpdate.Password = baseSvc.EnMD5(user.Password);
                userUpdate.LastModified = DateTime.Now;
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Get list user by role id
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public List<Security_User> GetListUserByRole(int roleId)
        {
            using (var data = new DataDataContext())
            {
                var item = data.Security_Users.Where(p => p.RoleID == roleId).ToList();
                return item;
            }
        }


        /// <summary>
        /// Login User
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public SCSService.Security_User LogInUser(string userName, string password)
        {
            using (var data = new DataDataContext())
            {
                BaseService baseSvc = new BaseService();
                var user = data.Security_Users.Where(p => p.UserName == userName && p.Password == baseSvc.EnMD5(password) && p.UserStatus==1).SingleOrDefault();
                return user;
            }
        }

        /// <summary>
        /// Get User by userName and Email
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="email"></param>
        /// <returns></returns>
        public SCSService.Security_User GetUserByUserNameAndEmail(string userName, string email)
        {
            using (var data = new DataDataContext())
            {
                var user = data.Security_Users.Where(p => p.UserName == userName && p.Email == email).SingleOrDefault();
                return user;
            }
        }

        /// <summary>
        /// Get Count User by Username
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int GetUserByUserName(string userName)
        {
            using (var data = new DataDataContext())
            {
                var user = data.Security_Users.Where(p => p.UserName == userName);

                return user.Count();
            }
        }

        /// <summary>
        /// Get Count Website by domain
        /// </summary>
        /// <param name="domain"></param>
        /// <returns></returns>
        public int GetWebsiteByDomain(string domain)
        {
            using (var data = new DataDataContext())
            {
                var user = data.System_Websites.Where(p => p.domain == domain);

                return user.Count();
            }
        }

        /// <summary>
        /// Get count user by Email
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public int GetUserbyEmail(string email)
        {
            using (var data = new DataDataContext())
            {
                var user = data.Security_Users.Where(p => p.Email == email);
                return user.Count();
            }
        }
        /// <summary>
        /// Get Permission of User when login
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Security_Permission GetPermissionByUserID(int id)
        {
            using (var data = new DataDataContext())
            {
                var per = (from u in data.Security_Users
                           join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                           from t0 in e0.DefaultIfEmpty()
                           join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                           from t1 in e1.DefaultIfEmpty()
                           join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                           from t2 in e2.DefaultIfEmpty()
                           where u.UserID == id
                           orderby t0.Priority ascending
                           select t2).FirstOrDefault();

                return per;
            }
        }

        /// <summary>
        /// Get Role top by user id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Security_Role GetRoleUser(int id)
        {
            using (var data = new DataDataContext())
            {
                var role = (from u in data.Security_Users
                            join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                            from t0 in e0.DefaultIfEmpty()
                            where u.UserID == id
                            orderby t0.Priority ascending
                            select t0).FirstOrDefault();

                return role;
            }
        }

        /// <summary>
        /// Create role on System
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public int CreateRole(Security_Role role)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Roles.InsertOnSubmit(role);
                data.SubmitChanges();

                return role.RoleID;
            }
        }

        /// <summary>
        /// Update role
        /// </summary>
        /// <param name="role"></param>
        public void UpdateRole(Security_Role role)
        {
            using (var data = new DataDataContext())
            {
                var roleUpdate = data.Security_Roles.Where(p => p.RoleID == role.RoleID).SingleOrDefault();

                roleUpdate.RoleName = role.RoleName;
                roleUpdate.RoleDes = role.RoleDes;
                roleUpdate.RoleStatus = role.RoleStatus;

                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Create Log when User LogIn
        /// </summary>
        /// <param name="log"></param>
        /// <returns></returns>
        public int CreateLog(Security_User_Log log)
        {
            using (var data = new DataDataContext())
            {
                data.Security_User_Logs.InsertOnSubmit(log);
                data.SubmitChanges();

                return log.LogID;
            }
        }

        /// <summary>
        /// Create Security User
        /// </summary>
        /// <param name="webUser"></param>
        public void CreateWebsiteUser(Security_Website_User webUser)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Website_Users.InsertOnSubmit(webUser);
                data.SubmitChanges();

            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="webRole"></param>
        public void CreateWebsiteRole(Sercurity_Website_Role webRole)
        {
            using (var data = new DataDataContext())
            {
                data.Sercurity_Website_Roles.InsertOnSubmit(webRole);
                data.SubmitChanges();

            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="webPer"></param>
        public void CreateWebsitePermission(Security_Website_Permission webPer)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Website_Permissions.InsertOnSubmit(webPer);
                data.SubmitChanges();

            }
        }
        /// <summary>
        /// Get Security role by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SCSService.Security_Role GetRoleByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var role = (from p in data.Security_Roles
                            where p.RoleID == id
                            select p).FirstOrDefault();
                return role;
            }
        }


        /// <summary>
        /// Get Security per by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SCSService.Security_Permission GetPermissionByID(int id)
        {
            using (var data = new DataDataContext())
            {
                var per = (from p in data.Security_Permissions
                           where p.PerID == id
                           select p).FirstOrDefault();
                return per;
            }
        }

        /// <summary>
        /// Create Security Permission
        /// </summary>
        /// <param name="per"></param>
        /// <returns></returns>
        public int CreateSecurityPermission(SCSService.Security_Permission per)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Permissions.InsertOnSubmit(per);
                data.SubmitChanges();

                return per.PerID;
            }
        }
        /// <summary>
        /// Update Permission by id
        /// </summary>
        /// <param name="per"></param>
        public void UpdateSecurityPermission(SCSService.Security_Permission per)
        {
            using (var data = new DataDataContext())
            {
                var perUpdate = data.Security_Permissions.Where(p => p.PerID == per.PerID).SingleOrDefault();

                perUpdate.PerName = per.PerName;
                perUpdate.PerDes = per.PerDes;
                perUpdate.ParentID = per.ParentID;
                perUpdate.PerStatus = per.PerStatus;

                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete Sercurity Permission
        /// </summary>
        /// <param name="id"></param>
        public void DeleteSecurityPermission(int id)
        {
            using (var data = new DataDataContext())
            {
                var perDelete = data.Security_Permissions.Where(p => p.PerID == id).SingleOrDefault();

                data.Security_Permissions.DeleteOnSubmit(perDelete);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete Sercurity role
        /// </summary>
        /// <param name="id"></param>
        public void DeleteSecurityRole(int id)
        {
            using (var data = new DataDataContext())
            {
                var roleDelete = data.Security_Roles.Where(p => p.RoleID == id).SingleOrDefault();

                data.Security_Roles.DeleteOnSubmit(roleDelete);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List Security Role
        /// </summary>
        /// <returns></returns>
        public List<SCSService.Security_Role> GetListRole(string RoleName, string RoleDes, byte? RoleStatus, int? Priority,int? siteGuid)
        {
            using (var data = new DataDataContext())
            {
                if (siteGuid != null)
                {
                    var list = (from p in data.Security_Roles
                                join swr in data.Sercurity_Website_Roles on p.RoleID equals swr.RoleID
                                where swr.SiteGuid==siteGuid
                                select p);

                    if (RoleName != string.Empty && RoleName != null)
                    {
                        list = list.Where(p => p.RoleName.Contains(RoleName));
                    }

                    if (RoleDes != string.Empty && RoleDes != null)
                    {
                        list = list.Where(p => p.RoleDes.Contains(RoleDes));
                    }

                    if (RoleStatus != null)
                    {
                        list = list.Where(p => p.RoleStatus == RoleStatus);
                    }

                    if (Priority != null)
                    {
                        list = list.Where(p => p.Priority == Priority);
                    }

                    return list.ToList();
                }
                else
                {
                    var list = (from p in data.Security_Roles
                                join swr in data.Sercurity_Website_Roles on p.RoleID equals swr.RoleID
                                select p);

                    if (RoleName != string.Empty && RoleName != null)
                    {
                        list = list.Where(p => p.RoleName.Contains(RoleName));
                    }

                    if (RoleDes != string.Empty && RoleDes != null)
                    {
                        list = list.Where(p => p.RoleDes.Contains(RoleDes));
                    }

                    if (RoleStatus != null)
                    {
                        list = list.Where(p => p.RoleStatus == RoleStatus);
                    }

                    if (Priority != null)
                    {
                        list = list.Where(p => p.Priority == Priority);
                    }

                    return list.ToList();
                }
                
            }
        }
        /// <summary>
        /// Get List Permission By ParentID
        /// </summary>
        /// <param name="parentID"></param>
        /// <returns></returns>
        public List<SCSService.Security_Permission> GetListSecurityPermissionByParentID(int? parentID)
        {
            using (var data = new DataDataContext())
            {
                var list = (from p in data.Security_Permissions
                            select p);

                if (parentID != null)
                {
                    list = list.Where(p => p.ParentID==parentID);
                }

                return list.ToList();
            }
        }
        /// <summary>
        /// Create Security Role Permission
        /// </summary>
        /// <param name="srp"></param>
        public void CreateSecurityRolePermission(SCSService.Security_Role_Permission srp)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Role_Permissions.InsertOnSubmit(srp);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete Security role Permission
        /// </summary>
        /// <param name="perID"></param>
        /// <param name="roleID"></param>
        public void DeleteSecurityRolePermission(int perID,int roleID)
        {
            using (var data = new DataDataContext())
            {
                var rolePerDelete = (from p in data.Security_Role_Permissions
                                     where p.PerID == perID && p.RoleID == roleID
                                     select p).FirstOrDefault();

                data.Security_Role_Permissions.DeleteOnSubmit(rolePerDelete);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// check Security role Permission exists (false is exists , true is not exist)
        /// </summary>
        /// <param name="perID"></param>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public bool CheckRolePermissionExists(int perID, int roleID)
        {
            using (var data = new DataDataContext())
            {
                bool result = false;
                var rolePerDelete = (from p in data.Security_Role_Permissions
                                     where p.PerID == perID && p.RoleID == roleID
                                     select p);

                if (rolePerDelete.Count() > 0)
                {
                    result = false;
                }
                else
                    result = true;

                return result;
            }
        }
        /// <summary>
        /// Get List Permission by UserID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<SCSService.Security_Permission> GetListPermissionByUser(int id)
        {
            using (var data = new DataDataContext())
            {
                var per = (from u in data.Security_Users
                           join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                           from t0 in e0.DefaultIfEmpty()
                           join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                           from t1 in e1.DefaultIfEmpty()
                           join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                           from t2 in e2.DefaultIfEmpty()
                           where u.UserID == id
                           orderby t0.Priority ascending
                           select t2);

                return per.ToList();
            }
        }
        /// <summary>
        /// Update Uer Log
        /// </summary>
        /// <param name="log"></param>
        public void UpdateUserLog(SCSService.Security_User_Log log)
        {
            using (var data = new DataDataContext())
            {
                var logUpdate = data.Security_User_Logs.Where(p => p.LogID == log.LogID).SingleOrDefault();

                //logUpdate.UserID = log.UserID;
                logUpdate.LogoutDate = log.LogoutDate;
                logUpdate.LogFile = log.LogFile;
                logUpdate.LogDetail = log.LogDetail;
                logUpdate.IpLog = log.IpLog;

                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Delete User Log
        /// </summary>
        /// <param name="id"></param>
        public void DeletetUserLog(int id)
        {
            using (var data = new DataDataContext())
            {
                var logDelete = data.Security_User_Logs.Where(p => p.LogID == id).SingleOrDefault();

                data.Security_User_Logs.DeleteOnSubmit(logDelete);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List Child of User Login in siteGuid
        /// </summary>
        /// <param name="idLogin"></param>
        /// <param name="priorityLogin"></param>
        /// <param name="siteGuid"></param>
        /// <param name="UserName"></param>
        /// <param name="Email"></param>
        /// <param name="UserStatus"></param>
        /// <returns></returns>
        public List<SCSService.Security_User> GetListUserByUserIDLogin(int? idLogin, int? priorityLogin,int? siteGuid, string UserName, string Email, byte? UserStatus)
        {
            using (var data = new DataDataContext())
            {
                if (siteGuid != null)
                {
                    var list = (from u in data.Security_Users
                                join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                                from t0 in e0.DefaultIfEmpty()
                                join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                                from t1 in e1.DefaultIfEmpty()
                                join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                                from t2 in e2.DefaultIfEmpty()
                                join swu in data.Security_Website_Users on u.UserID equals swu.UserID into e3
                                from t3 in e3.DefaultIfEmpty()
                                where t0.Priority > priorityLogin && u.UserID != idLogin && t3.SiteGuid == siteGuid
                                orderby t0.Priority ascending
                                select u);

                    if (UserName != string.Empty && UserName != null)
                    {
                        list = list.Where(p => p.UserName.Contains(UserName));
                    }

                    if (Email != string.Empty && Email != null)
                    {
                        list = list.Where(p => p.Email.Contains(Email));
                    }

                    if (UserStatus != null)
                    {
                        list = list.Where(p => p.UserStatus == UserStatus);
                    }

                    return list.ToList();
                }
                else
                {
                    var list = (from u in data.Security_Users
                                join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                                from t0 in e0.DefaultIfEmpty()
                                join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                                from t1 in e1.DefaultIfEmpty()
                                join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                                from t2 in e2.DefaultIfEmpty()
                                join swu in data.Security_Website_Users on u.UserID equals swu.UserID into e3
                                from t3 in e3.DefaultIfEmpty()
                                where t0.Priority > priorityLogin && u.UserID != idLogin
                                orderby t0.Priority ascending
                                select u);

                    if (UserName != string.Empty && UserName != null)
                    {
                        list = list.Where(p => p.UserName.Contains(UserName));
                    }

                    if (Email != string.Empty && Email != null)
                    {
                        list = list.Where(p => p.Email.Contains(Email));
                    }

                    if (UserStatus != null)
                    {
                        list = list.Where(p => p.UserStatus == UserStatus);
                    }

                    return list.ToList();
                }
                
            }
        }
        /// <summary>
        /// Update UserStatus
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateUserStatus(int? id, int? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var users = (from p in data.Security_Users
                                   where p.UserID == id
                                   select p).FirstOrDefault();
                    if (users != null)
                    {
                        if (users.UserStatus != newStatus || users.UserStatus == null)
                            users.UserStatus = (byte)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }

        /// <summary>
        /// Update Role status
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdateRoleStatus(int? id, int? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var role = (from p in data.Security_Roles
                                 where p.RoleID == id
                                 select p).FirstOrDefault();
                    if (role != null)
                    {
                        if (role.RoleStatus != newStatus || role.RoleStatus == null)
                            role.RoleStatus = (byte)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }
        /// <summary>
        /// Get List Permission
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<SCSService.Security_Permission> GetListPermission(string PerName, string PerDes, int? ParentID, byte? PerStatus,int? siteGuid)
        {
            using (var data = new DataDataContext())
            {
                if (siteGuid != null)
                {
                    var per = (from p in data.Security_Permissions
                               join swp in data.Security_Website_Permissions on p.PerID equals swp.PerID
                               where swp.SiteGuid==siteGuid
                               select p);

                    if (PerName != string.Empty && PerName != null)
                    {
                        per = per.Where(p => p.PerName.Contains(PerName));
                    }

                    if (PerDes != string.Empty && PerDes != null)
                    {
                        per = per.Where(p => p.PerDes.Contains(PerDes));
                    }

                    if (ParentID != null)
                    {
                        per = per.Where(p => p.ParentID == ParentID);
                    }

                    if (PerStatus != null)
                    {
                        per = per.Where(p => p.PerStatus == PerStatus);
                    }

                    return per.ToList();
                }
                else
                {
                    var per = (from p in data.Security_Permissions
                               select p);

                    if (PerName != string.Empty && PerName != null)
                    {
                        per = per.Where(p => p.PerName.Contains(PerName));
                    }

                    if (PerDes != string.Empty && PerDes != null)
                    {
                        per = per.Where(p => p.PerDes.Contains(PerDes));
                    }

                    if (ParentID != null)
                    {
                        per = per.Where(p => p.ParentID == ParentID);
                    }

                    if (PerStatus != null)
                    {
                        per = per.Where(p => p.PerStatus == PerStatus);
                    }

                    return per.ToList();
                }
                
            }
        }

        /// <summary>
        /// Update PermissionStatus
        /// </summary>
        /// <param name="id"></param>
        /// <param name="newStatus"></param>
        public void UpdatePermissionStatus(int? id, int? newStatus)
        {
            using (var data = new DataDataContext())
            {
                if (id != null && newStatus != null)
                {
                    var per = (from p in data.Security_Permissions
                                where p.PerID == id
                                select p).FirstOrDefault();
                    if (per != null)
                    {
                        if (per.PerStatus != newStatus || per.PerStatus == null)
                            per.PerStatus = (byte)newStatus;

                        data.SubmitChanges();
                    }
                }

            }
        }

        /// <summary>
        /// Get List Security role Permission by RoleID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<SCSService.Security_Role_Permission> GetListSecurityRolePermissionByRoleID(int id)
        {
            using (var data = new DataDataContext())
            {
                var list = (from u in data.Security_Users
                            join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                            from t0 in e0.DefaultIfEmpty()
                            join rp in data.Security_Role_Permissions on t0.RoleID equals rp.RoleID into e1
                            from t1 in e1.DefaultIfEmpty()
                            join p in data.Security_Permissions on t1.PerID equals p.PerID into e2
                            from t2 in e2.DefaultIfEmpty()
                            where t1.RoleID==id
                            orderby t0.Priority ascending
                            select t1);

                
                return list.ToList();
            }
        }
        /// <summary>
        /// Get List Role in system by priority of User Login
        /// </summary>
        /// <param name="priority"></param>
        /// <returns></returns>
        public List<SCSService.Security_Role> GetListRoleByPriority(int priority,int? siteGuid)
        {
            using (var data = new DataDataContext())
            {
                if (siteGuid != null)
                {
                    var list = (from p in data.Security_Roles
                                join swr in data.Sercurity_Website_Roles on p.RoleID equals swr.RoleID
                                where p.Priority > priority && swr.SiteGuid==siteGuid
                                select p);
                    return list.ToList();
                }
                else
                {
                    var list = (from p in data.Security_Roles
                                where p.Priority > priority
                                select p);
                    return list.ToList();
                }
                
            }
        }
        /// <summary>
        /// Delete User
        /// </summary>
        /// <param name="id"></param>
        public void DeleteUser(int id)
        {
            using (var data = new DataDataContext())
            {
                var users = (from p in data.Security_Users
                             where p.UserID == id
                             select p).FirstOrDefault();

                data.Security_Users.DeleteOnSubmit(users);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Get List Module Permission by PerID
        /// </summary>
        /// <param name="PerId"></param>
        /// <returns></returns>
        public List<SCSService.Security_Module_Permission> GetListModulePermissionByPerID(int PerId)
        {
            using (var data = new DataDataContext())
            {
                var item = data.Security_Module_Permissions.Where(p => p.PerID == PerId).ToList();
                return item;
            }
        }
        /// <summary>
        /// Delete modle permission
        /// </summary>
        /// <param name="moduleID"></param>
        /// <param name="perID"></param>
        public void DeleteModulePermission(int moduleID,int perID)
        {
            using (var data = new DataDataContext())
            {
                var modulePermission = (from p in data.Security_Module_Permissions
                             where p.ModulID == moduleID && p.PerID == perID
                             select p).FirstOrDefault();

                data.Security_Module_Permissions.DeleteOnSubmit(modulePermission);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Create Module Permission
        /// </summary>
        /// <param name="smp"></param>
        public void CreateModulePermission(SCSService.Security_Module_Permission smp)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Module_Permissions.InsertOnSubmit(smp);
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Get list PagePermission by PerID
        /// </summary>
        /// <param name="PerId"></param>
        /// <returns></returns>
        public List<SCSService.Security_Page_Permission> GetListPagePermissionByPerID(int PerId)
        {
            using (var data = new DataDataContext())
            {
                var item = data.Security_Page_Permissions.Where(p => p.PerID == PerId).ToList();
                return item;
            }
        }
        /// <summary>
        /// Delete Page Permission
        /// </summary>
        /// <param name="pageID"></param>
        /// <param name="perID"></param>
        public void DeletePagePermission(int pageID, int perID)
        {
            using (var data = new DataDataContext())
            {
                var pagePermission = (from p in data.Security_Page_Permissions
                                        where p.PageID == pageID && p.PerID == perID
                                        select p).FirstOrDefault();

                data.Security_Page_Permissions.DeleteOnSubmit(pagePermission);
                data.SubmitChanges();
            }
        }
        /// <summary>
        /// Create Page Permission
        /// </summary>
        /// <param name="spp"></param>
        public void CreatePagePermission(SCSService.Security_Page_Permission spp)
        {
            using (var data = new DataDataContext())
            {
                data.Security_Page_Permissions.InsertOnSubmit(spp);
                data.SubmitChanges();
            }
        }

        /// <summary>
        /// Get List Order for Customer
        /// </summary>
        /// <param name="id"></param>
        /// <param name="status"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="siteGuid"></param>
        /// <returns></returns>
        public List<Customer_Order> GetListOrderByCustomerID(int? id, int? status, DateTime? startDate, DateTime? endDate, int siteGuid, string order_search)
        {
            using (var data = new DataDataContext())
            {
                var listOrder = (from p in data.Customer_Orders
                                 where p.SiteGuid==siteGuid
                                 select p);
                if (id != null && id != 0)
                {
                    listOrder = listOrder.Where(p => p.CustomerID == id);
                }

                if (status != null)
                {
                    listOrder = listOrder.Where(p => p.OrderStatus == status);
                }

                if (startDate != null && endDate == null)
                {
                    listOrder = listOrder.Where(p => p.CreateDate >= startDate);
                }

                if (startDate == null && endDate != null)
                {
                    listOrder = listOrder.Where(p => p.CreateDate <= endDate);
                }

                if (startDate != null && endDate != null)
                {
                    listOrder = listOrder.Where(p => p.CreateDate >= startDate && p.CreateDate <= endDate);
                }

                if (!string.IsNullOrEmpty(order_search))
                {
                    var list_invoice = (from p in data.Customer_Orders
                                        join o in data.Invoices on p.OrderID equals o.OrderID
                                        where listOrder.Contains(p) && o.InvoiceCode==order_search
                                        select p.OrderID).ToList();
                    listOrder = listOrder.Where(p => list_invoice.Contains(p.OrderID));
                }

                listOrder = listOrder.OrderByDescending(p => p.OrderID);

                return listOrder.ToList();
            }
        }

        public List<Detail_Order> CustomerOrderDetail(int? id)
        {
            using (var data = new DataDataContext())
            {


                List<Detail_Order> result = (from p in data.Customer_Orders
                                             join ot in data.Order_Items on p.OrderID equals ot.OrderID into r0
                                             from e0 in r0.DefaultIfEmpty()
                                             join oid in data.Order_Item_Details on e0.OrderItemID equals oid.OrderItemID into r2
                                             from e1 in r2.DefaultIfEmpty()
                                             join psd in data.Product_Stock_Details on e1.ProductStockDetailID equals psd.ProductStockDetailID into r3
                                             from e2 in r3.DefaultIfEmpty()
                                             join invo in data.Invoices on p.OrderID equals invo.OrderID into r4
                                             from e3 in r4.DefaultIfEmpty()
                                             join pa in data.Payments on e3.InvoiceID equals pa.InvoiceID 
                                             where p.OrderID == id && pa.Status==1 && e3.Status==1
                                             select new Detail_Order
                                             {
                                                 OrderID = p.OrderID,
                                                 CreateDate = p.CreateDate,
                                                 FullName = p.Fullname,
                                                 StatusOrder = p.OrderStatus,
                                                 Total = p.Total,
                                                 Email = p.Email,
                                                 Phone = p.Phone,
                                                 ProductID = e0.ProductID,
                                                 OrderItemID = e0.OrderItemID,
                                                 Code = e2.Code,
                                                 Serial = e2.Serial
                                             }).ToList();
                return result;
            }

        }

        public Security_Role GetRoleSystemByUser(int id)
        {
            using (var data = new DataDataContext())
            {
                var role = (from u in data.Security_Users
                            join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                            from t0 in e0.DefaultIfEmpty()
                            where u.UserID == id && t0.SiteGuidCreated == null
                            orderby t0.Priority ascending
                            select t0).FirstOrDefault();

                return role;
            }
        }

        public Security_Role GetRoleLocalSiteByUser(int id, int siteGuid)
        {
            using (var data = new DataDataContext())
            {
                var role = (from u in data.Security_Users
                            join r in data.Security_Roles on u.RoleID equals r.RoleID into e0
                            from t0 in e0.DefaultIfEmpty()
                            where u.UserID == id && t0.SiteGuidCreated == siteGuid
                            orderby t0.Priority ascending
                            select t0).FirstOrDefault();

                return role;
            }
        }
    }
}
